package com.cbs.java.component.database;

import java.beans.PropertyDescriptor;
import java.lang.reflect.Constructor;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by brochexu on 8/7/15.
 */
public abstract class DefaultDatabase implements Database {
    protected abstract Connection getConnection();

    @Override
    public <T> List<T> query(String sql, Class<T> classOfT) throws Exception {
        Connection connection = getConnection();
        List<T> results = new ArrayList<>();
        try {
            Constructor<T> constructor = classOfT.getDeclaredConstructor();
            constructor.setAccessible(true);

            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet rs = statement.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            while (rs.next()) {
                T item = constructor.newInstance();
                for (int i = 1; i < columnCount + 1; i++) {
                    String columnName = rsmd.getColumnName(i);
                    try {
                        PropertyDescriptor propertyDescriptor = new PropertyDescriptor(columnName, classOfT);
                        Method writeMethod = propertyDescriptor.getWriteMethod();
                        writeMethod.invoke(item, rs.getObject(i));
                    } catch (Exception e) {
                    }
                }
                results.add(item);
            }
            rs.close();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace(System.err);
        }

        return results;
    }

    @Override
    public <T> T fetch(String sql, Class<T> classOfT) throws Exception {
        Connection connection = getConnection();
        T result = null;
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet rs = statement.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            if (rs.next()) {
                Constructor<T> constructor = classOfT.getDeclaredConstructor();
                constructor.setAccessible(true);
                result = constructor.newInstance();
                for (int i = 1; i < columnCount + 1; i++) {
                    String columnName = rsmd.getColumnName(i);
                    try {
                        PropertyDescriptor propertyDescriptor = new PropertyDescriptor(columnName, classOfT);
                        Method writeMethod = propertyDescriptor.getWriteMethod();
                        writeMethod.invoke(result, rs.getObject(i));
                    } catch (Exception e) {
                    }
                }
            }
            rs.close();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace(System.err);
        }
        return result;
    }

    @Override
    public <T> T scalar(String sql, Class<T> classOfT) throws Exception {
        Connection connection = getConnection();
        T result = null;
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                result = (T) rs.getObject(1);
            }
            rs.close();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace(System.err);
        }
        return result;
    }

    @Override
    public void execute(String sql) {
        Connection connection = getConnection();
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.executeUpdate();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace(System.err);
        }
    }

    @Override
    public int insert(String sql) {
        Connection connection = getConnection();
        int id = -1;
        try {
            PreparedStatement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            statement.executeUpdate();
            ResultSet resultSet = statement.getGeneratedKeys();
            if (resultSet != null && resultSet.next()) {
                id = resultSet.getInt(1);
            }
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace(System.err);
        }
        return id;
    }

    @Override
    public int update(String sql) {
        Connection connection = getConnection();
        int count = 0;
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            count = statement.executeUpdate();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace(System.err);
        }
        return count;
    }

    @Override
    public int delete(String sql) {
        return update(sql);
    }

    @Override
    public void executeBatch(List<String> sqls) {
        Connection connection = getConnection();
        if (sqls == null) {
            return;
        }
        try {
            connection.setAutoCommit(false);
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            Statement statement = connection.createStatement();
            for (String sql : sqls) {
                statement.executeUpdate(sql);
            }
            connection.commit();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace(System.err);
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException e1) {
                    e1.printStackTrace(System.err);
                }
            }
        }
    }
}
